package me.denger.database2Word.service;

import me.denger.database2Word.bean.Column;
import me.denger.database2Word.bean.Table;
import me.denger.database2Word.config.ConfigBean;

import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * oracle的实现
 *
 * @author 登琼
 */
public class DatabaseImp4Oracle extends DatabaseBaseAbstract {

    public DatabaseImp4Oracle(ConfigBean configBean) throws ClassNotFoundException, SQLException {
        super(configBean);
    }

    public DatabaseImp4Oracle(String driver, String url, String userName, String password)
            throws ClassNotFoundException, SQLException {
        super(driver, url, userName, password);
    }

    @Override
    public List<Table> getTables(String tableNamePrefix) throws SQLException {
        List<Table> tableList = getTableNames(tableNamePrefix);
        for (Table table : tableList) {
            List<Column> columnList = getColumn4Oracle(table.getName());
            table.setColumnList(columnList);
        }
        return tableList;
    }

    /**
     * 获取表中字段的所有注释
     *
     * @param tableName 表名
     * @return
     */
    private List<Column> getColumn4Oracle(String tableName) throws SQLException {
        List<Column> columnList = new ArrayList<>();
        // 与数据库的连接
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            statement = getConnection().prepareStatement(
                    "SELECT T .COLUMN_NAME, T .DATA_TYPE || '(' || T .DATA_LENGTH || ')' TYPE, T .NULLABLE, T .DATA_DEFAULT, C.COMMENTS FROM USER_TAB_COLUMNS T LEFT JOIN USER_COL_COMMENTS C ON T .COLUMN_NAME = C.COLUMN_NAME  AND T.TABLE_NAME=C.TABLE_NAME WHERE T .TABLE_NAME = UPPER (?)");
            statement.setString(1, tableName);
            rs = statement.executeQuery();


            while (rs.next()) {
                Column column = new Column();
                column.setName(rs.getString("COLUMN_NAME"));
                column.setType(rs.getString("TYPE"));
                column.setComment(rs.getString("COMMENTS"));

                String isNull = rs.getString("NULLABLE");
                if ("N".equals(isNull)) {
                    column.setNullAble(false);
                } else {
                    column.setNullAble(true);
                }

                // 这里有一个问题就是获取该默认值的出现异常，所以注释了，正在寻找解决，2018年5月9日 10点07分
                Object dataDefault = "";
                try {
                    dataDefault = rs.getObject("DATA_DEFAULT");
                } catch (Exception e) {
                    try {
                        dataDefault = rs.getLong("DATA_DEFAULT");
                    } catch (Exception e1) {
                        try {
                            dataDefault = rs.getInt("DATA_DEFAULT");
                        } catch (Exception e2) {
                            try {
                                dataDefault = rs.getDouble("DATA_DEFAULT");
                            } catch (Exception e3) {
                                try {
                                    dataDefault = rs.getFloat("DATA_DEFAULT");
                                } catch (Exception e4) {
                                    try {
                                        dataDefault = rs.getBigDecimal("DATA_DEFAULT");
                                    } catch (Exception e5) {
                                        try {
                                            dataDefault = rs.getByte("DATA_DEFAULT");
                                        } catch (Exception e6) {
                                            e6.printStackTrace();
                                        }
                                    }
                                }
                            }
                        }
                    }
                }

                System.out.println(column.getName() + "---------------" + dataDefault);
                if (null != dataDefault) {
                    column.setDefaultValue(dataDefault + "");
                }

                columnList.add(column);
            }

            // 获取主键
            getPrimaryKeys(tableName, columnList);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return columnList;
    }

    /**
     * 获取表中字段的所有主键
     *
     * @param tableName
     * @return
     */
    private List<Column> getPrimaryKeys(String tableName, List<Column> columnList) {

        // 与数据库的连接
        ResultSet rs = null;
        try {
            DatabaseMetaData metaData = getConnection().getMetaData();
            rs = metaData.getPrimaryKeys(null, null, tableName);
            while (rs.next()) {
                // primaryKeys.add(rs.getString(4));
                // System.err.println("****** " + tableName + " ******");
                // System.err.println("TABLE_CAT : " + rs.getObject(1));
                // System.err.println("TABLE_SCHEM: " + rs.getObject(2));
                // System.err.println("TABLE_NAME : " + rs.getObject(3));
                // System.err.println("COLUMN_NAME: " + rs.getObject(4));
                // System.err.println("KEY_SEQ : " + rs.getObject(5));
                // System.err.println("PK_NAME : " + rs.getObject(6));
                // System.err.println("****** ******* ******");

                // 设置主键
                for (Column column : columnList) {
                    if (column.getName().equals(rs.getString(4))) {
                        column.setKey(true);
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return columnList;
    }
}
